package cz.acies.uniql.dialect;

import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

//import com.smart4web.db.structure.FieldMetaData;
//import com.smart4web.db.structure.PrimaryKeyMetaData;
//import com.smart4web.db.structure.TableMetaData;

import cz.acies.uniql.connect.SQLConnect;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.IndexesMetaData;
import cz.acies.uniql.sql.SelectQuery;

public class DB2Dialect extends Dialect
{	
	// DBMetaData variables
	// TableMetaData variables
	// FieldMetadata variables
	// PrimaryKeyMetaData variables
	// IndexMetaData variables
	
	// Convert Element variables
	// Convert values variables

	public DB2Dialect() {
		super();
		
		registerColumnType( Types.BIT, "integer" );
		registerColumnType( Types.BIGINT, "bigint" );
		registerColumnType( Types.SMALLINT, "integer" );
		registerColumnType( Types.TINYINT, "integer" );
		registerColumnType( Types.INTEGER, "integer" );
		registerColumnType( Types.CHAR, "varchar($l)" );
		registerColumnType( Types.VARCHAR, "varchar($l)" );
		registerColumnType( Types.FLOAT, "double" );
		registerColumnType( Types.DOUBLE, "double" );
		registerColumnType( Types.DATE, "date" );
		registerColumnType( Types.TIME, "time" );
		registerColumnType( Types.TIMESTAMP, "timestamp" );
		registerColumnType( Types.VARBINARY, "varchar($l)" );
		registerColumnType( Types.NUMERIC, "double" );
		registerColumnType( Types.DECIMAL, "double" );
		registerColumnType( Types.BLOB, "blob($l)" );
		registerColumnType( Types.CLOB, "clob($l)" );
		registerColumnType( Types.LONGVARCHAR, "clob($l)" );
		
		// DBMetaData variables
		// TableMetaData variables
		// FieldMetadata variables
		FMD_APPEND_DEFAULT_IFNULL = false;
		FMD_IDENTITY_BUILD_PART = "GENERATED ALWAYS AS IDENTITY";
		//FMD_NULL_COLUMN_STRING = "NULL";
		
		// PrimaryKeyMetaData variables
		PK_CONSTRAINTED = true;
		
		// IndexMetaData variables		
		// Convert Element variables
		// Convert values variables
	}

	// ----- Quoted methods ---------------------------------------------------
	
	public String LQ() { return "\""; }
	public String RQ() { return "\""; }
	public String CTI_LQ() { return ""; }
	public String CTI_RQ() { return ""; }
	
	// ----- Database connection methods --------------------------------------
	
	public String getSQLConnPrefix() {
		return "jdbc:oracle:thin:";
	}

	public String getSQLConnDriver() {
		return "com.ibm.db2.jcc.DB2Driver";
	}
	
	public String generateSQLConnUrl(SQLConnect sqlconn) throws SQLException {
		
		String prefix = sqlconn.getPrefix();
		String host = sqlconn.getHost();
		String database = sqlconn.getDatabase();
	
		String schema = database.toUpperCase();
		if (schema != null) {
			sqlconn.setSQLConnSchema(schema);
			this.setSchema(schema);
		}
		sqlconn.getProperties().remove("host");
		sqlconn.getProperties().remove("port");
		sqlconn.getProperties().remove("database");
		
		String url = prefix+""+host;
		//System.out.println("DB2Dialect.generateSQLConnUrl().url = "+url);
		return url;
	}

	public Properties parseURL(String url) {
		try {
			Properties prop = new Properties();
			
			String shl = "";
			String shr = "";
			
			int pos = url.indexOf("?");
			if (pos > -1) {
				shl = url.substring(0, pos);
				shr = url.substring(pos+1);
			} else {
				shl = url;
			}
			if (shl != null && shl.length() > 0) {
				pos = shl.indexOf("//");
				if (pos > -1) {
					String prefix = shl.substring(0, pos);
					shl = shl.substring(pos+2);
					prop.put("prefix", prefix);
				} 
				pos = shl.indexOf("/");
				if (pos > -1) {
					String host = shl.substring(0, pos);
					shl = shl.substring(pos+1);
					prop.put("host", host);
				}
				if (shl.length() > 0) {
					String database = shl;
					prop.put("database", database);
				}
			}
			if (shr != null && shr.length() > 0) {
				StringTokenizer st = new StringTokenizer(shr,"&");
				while (st.hasMoreTokens()) {
					String element = st.nextToken();
					pos = element.indexOf("=");
					if (pos > -1) {
						String key = element.substring(0, pos);
						String value = "";
						if (pos < element.length()) value = element.substring(pos+1, element.length());
						prop.put(key, value);
					}
				}
			}			
			return prop;
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	// -----  -----------------------------------------------------------------
	
	public synchronized Statement createStatement(Connection conn) throws SQLException {
		return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 	
	}

	public synchronized Statement createStatement(Connection conn, int style) throws SQLException {
		
		int type = ResultSet.TYPE_SCROLL_SENSITIVE;
		int concur = ResultSet.CONCUR_READ_ONLY;
		
		switch (style) {
			case 0: concur = ResultSet.CONCUR_READ_ONLY; break;
			case 1: concur = ResultSet.CONCUR_UPDATABLE; break;
			default:
				break;
		}
		return conn.createStatement(); 	
	}

	// ----- Convert values mothods -------------------------------------------
	
	// ----- ColumnType methods -----------------------------------------------
	
	// ----- ReservedKeywords methods -----------------------------------------
	
	// ----- Fn Methods -------------------------------------------------------
	
	//* ***********************************************************************
	//* Database Mapping methods
	//* ***********************************************************************
	
	// ----- DBMetaData methods -----------------------------------------------
	
	// ----- TableMetaData metdods --------------------------------------------
	
	// ----- FieldMetadata methods --------------------------------------------
	
	/*
	public int getIdentityColumnStyle() {
		return 0;
	}

	public String getIdentityColumnString() {
		return "NEXTVAL('identity_sequence')";
	}
	*/
	// ----- PrimaryKey Constraint --------------------------------------------
	/*
	public int getPKAppendType() {
		return Dialect.PK_APPEND_TYPE_COLUMN;
	}
	*/
	// ----- IndexMetaData methods --------------------------------------------
	
	/**
	 * Funkce vygeneruje n�zev pro INDEX k tabulce.
	 *  
	 */		
	public String generateIndexName(String tableName, String indexName, String sufix) {
		String schema = (this.getSchema() != null) ? this.getSchema() : "";
		String name = schema+".IDX_"+tableName.toUpperCase()+"_"+sufix.toUpperCase();
		return name;
	}

	public String getFromAsString() {
		return "";
	}

	public String transferAlias(String alias) {
		return alias.toUpperCase();
	}
	
	// ----- Convert Element methods ------------------------------------------
	
	// ----- Convert Values methods -------------------------------------------

	public String getValueDouble(String value) {
		/*
		try {
			value = Math2.decimalFormat(value);			
		} catch (Exception e) {
			
		}
		*/
		return value;
	}

	public String getValueInteger(String value) {  
		//value = Math2.numberFormat(value);
		return value;
	}

	public String text2SQL(String input)  {
		StringBuffer buf = new StringBuffer("");
		for (int i = 0; i < input.length(); i++) {
			if (input.charAt(i) == '\\') {
				buf.append("\\\\");
			} else if (input.charAt(i) == '\'') {
				buf.append("\'\'");
			} else {
				buf.append(input.charAt(i));
			}
		} return buf.toString();
	}

	
	// ----- DDL methods ------------------------------------------------------

	/**
	 * 
	 */		
	public String getDropTableString(String schema,String table) {
		schema = this.getSchema();
		return "DROP TABLE \""+schema+"\".\""+table+"\"";
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.dialect.Dialect#getCreateTableFieldString(com.smart4web.db.structure.FieldMetaData)
	 */
	public String getCreateTableFieldString(FieldMetaData fmd) {
		String s = "";
		if (fmd.isPrefix()) s += this.LQ() + fmd.getTable() + this.RQ() + ".";
		s += (isKeyword(fmd.getName())) ? 
		    this.LQ()+(fmd.getName()+"_")+this.RQ() : 
		    this.LQ()+fmd.getName()+this.RQ();
		    
		fmd.setTypeName(this.getColumnTypeString(
		    fmd.getSqlType(), fmd.getSize(), fmd.getDecimal())
		);
		s += " " + fmd.getTypeName() + " ";
	
		if (fmd.isNullable()) {
			if (fmd.hasValue()) {
				s += "DEFAULT " + fmd.getValue() + " ";
			}
			s += this.getNullColumnString();
		} else {
			s += "DEFAULT " + fmd.getValue() + " ";
			s += " NOT NULL ";
		}
		return s;
	}

	/**
	 * 
	 */	
	public String getCreateIndexesString(IndexesMetaData ixmd) {
		List<String> list = getCreateIndexesList(ixmd);
		String s = "";
		for (Iterator<String> e = list.iterator() ; e.hasNext() ;) {
			String query = (String)e.next();
			s += query + ((e.hasNext()) ? "; " : "");
		}	 		
		return s;
	}

	/**
	 * 
	 */
	public List<String> getCreateIndexesList(IndexesMetaData ixmd) {
		Vector list = new Vector();
		//String schema = (ixmd.getSchema() != null) ? ixmd.getSchema() : "" ;
		String schema = this.getSchema();
		for (Enumeration e = ixmd.elements(); e.hasMoreElements();) {
			Hashtable params = (Hashtable)e.nextElement();
			Hashtable columns = (Hashtable)params.get("columns");
			
			String name = (String)params.get("INDEX_NAME");
			//System.out.println("INDEX_NAME = "+name);	
			String query = "";
			if (name.equals("PRIMARY")) {
			} else {
				//String asc_desc = (String)params.get("ASC_OR_DESC");
				boolean nonunique = ((Boolean)params.get("NON_UNIQUE")).booleanValue();
				
				String colstr = "( ";
				for (int i = 0; i < columns.size(); i++) {
					String columnname = (String)columns.get(new Integer(i+1));
					//columnname = (ReservedKeywords.isReservedKeyword(columnname)) ? (columnname+"_") : columnname;
					colstr += LQ() + columnname + RQ(); 					
					if (i < columns.size()-1) colstr += ",";
				}
				colstr += " )";
								
				query = "CREATE ";
				if (!nonunique) { 
					query += "UNIQUE ";
				}
				// query += asc_desc;
				query += "INDEX "+name+" ON "+schema+".\""+ixmd.getTable().toUpperCase()+"\" "+colstr;
				list.addElement(query);
			}
		}
		return list;
	}
	
	/**
	 * 
	 */
	public String getDropIndexString(String tableName, String indexName) {
		return "DROP INDEX "+tableName+" ON ["+indexName+"]";
	} 

	// ----- FullText methods -------------------------------------------------

	// ----- LOCK & UNLOCK TABLE methtods -------------------------------------

	// ----- QueryRebuilder methods -------------------------------------------

}

/*
public static void main(String[] args) {
	SQLConnect db = null;
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		DB2Dialect dialect = new DB2Dialect();
		
		String url = "jdbc:db2://S4W/moeller?user=db2admin&password=smart";
		Properties prop = dialect.parseURL(url);
		System.out.println("info = "+prop);
		
		db = new SQLConnect(prop, dialect);			
		conn = db.openConnection();
		System.out.println("Opening connection...");
		System.out.println("schema = "+dialect.getSchema());
		
		SelectQuery sql = new SelectQuery(dialect).addFrom("ACC_DAY_BOOK");
		System.out.println("DB2Dialect.sql = "+sql.toSQLString());
		
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql.toSQLString());
		while (rs.next()) {
			System.out.println();
		}
		
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try { if (rs != null) rs.close(); } catch (Exception e) {}
		try { if (stmt != null) stmt.close(); } catch (Exception e) {}
		try { if (conn != null) conn.close(); } catch (Exception e) {}
		try { if (db != null) db.close();  } catch (Exception e) {}
	}
	System.out.println("");
}
*/
